AI SQL Generator Backend

A powerful Node.js backend service that converts natural language queries into SQL statements using AI. Features intelligent vector search, dual database support, and flexible embedding options for production-ready SQL generation.

πŸš€ Features

πŸ“‹ Prerequisites

πŸ› οΈ Installation

  1. Clone and navigate to the backend directory:

    cd backend/ai-sql-backend
    
  2. Install dependencies:

    npm install
    
  3. Configure environment variables:

    cp .env.example .env
    # Edit .env with your configuration
    
  4. Build the project:

    npm run build
    
  5. Start the server:

    npm start
    

βš™οΈ Configuration

Environment Variables

Create a .env file with the following variables:

# Embedding Method (local or azure)
EMBEDDING_METHOD=local

# Azure OpenAI Configuration (for production)
AZURE_OPENAI_KEY=your_azure_openai_key_here
AZURE_OPENAI_ENDPOINT=https://your-resource.openai.azure.com/
AZURE_OPENAI_DEPLOYMENT=gpt-4o-mini
AZURE_OPENAI_EMBEDDING=text-embedding-3-large
AZURE_OPENAI_API_VERSION=2024-08-01-preview

# Disable embeddings (fallback to keyword matching)
DISABLE_EMBEDDINGS=false

# Server Configuration
PORT=5050
NODE_ENV=development

# Schema File Paths
ENTITIES_SCHEMA_PATH=entities_prod_definition.txt
DMS_SCHEMA_PATH=dms_prod_definition.txt

Embedding Methods

1. Local Embeddings (Default)

2. Azure OpenAI Embeddings (Production)

3. Disabled Embeddings (Fallback)

πŸš€ Quick Start

Development (Local Embeddings)

# Set environment
export EMBEDDING_METHOD=local

# Start server
npm run dev

Production (Azure Embeddings)

# Set environment
export EMBEDDING_METHOD=azure
export AZURE_OPENAI_KEY=your_key
export AZURE_OPENAI_ENDPOINT=https://your-resource.openai.azure.com/

# Start server
npm start

πŸ“‘ API Endpoints

Core Endpoints

Method Endpoint Description
POST /api/sql Generate SQL from natural language
POST /api/ai/answer Get AI analysis and database selection
GET /api/vector/query Vector search for schema chunks
GET /api/vector-health Health check for vector service
GET /health Basic health check

Example Usage

Generate SQL

curl -X POST http://localhost:5050/api/sql \
  -H "Content-Type: application/json" \
  -d '{"question": "Top 10 organisations by revenue in 2024"}'

Get AI Analysis

curl -X POST http://localhost:5050/api/ai/answer \
  -H "Content-Type: application/json" \
  -d '{"question": "Show me all people in the marketing department"}'
curl "http://localhost:5050/api/vector/query?text=revenue organizations"

Health Check

curl http://localhost:5050/api/vector-health

πŸ“Š Response Format

SQL Generation Response

{
  "question": "Top 10 organisations by revenue in 2024",
  "db_name": "entities",
  "sql": "SELECT * FROM organisations ORDER BY revenue DESC LIMIT 10",
  "params": [],
  "notes": "Query targets the entities database and retrieves top organizations by revenue"
}

AI Analysis Response

{
  "db_name": "entities",
  "answer": "This query should target the entities database to find organization revenue data",
  "rationale": "The question asks about organizations and revenue, which are typically stored in the entities database",
  "plan": {
    "target": "entities",
    "tables": ["organisations"],
    "filters": ["revenue", "2024"]
  }
}

Health Check Response

{
  "status": "healthy",
  "vector_service": "integrated",
  "method": "local",
  "docs_loaded": 25,
  "embedder_ready": true,
  "embeddings_disabled": false
}

πŸ—οΈ Architecture

β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”    β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”    β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
β”‚   Frontend      │───▢│   Backend API    │───▢│  Vector Service β”‚
β”‚   (Next.js)     β”‚    β”‚   (Express)      β”‚    β”‚  (Local/Azure)  β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜    β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜    β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
                              β”‚
                              β–Ό
                       β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
                       β”‚  Azure OpenAI    β”‚
                       β”‚  (SQL Generation)β”‚
                       β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Key Components

πŸ”§ Development

Project Structure

src/
β”œβ”€β”€ config/
β”‚   └── env.ts              # Environment configuration
β”œβ”€β”€ services/
β”‚   β”œβ”€β”€ vectorService.ts    # Unified vector search service
β”‚   β”œβ”€β”€ answerService.ts    # AI analysis service
β”‚   β”œβ”€β”€ sqlGenerator.ts     # SQL generation service
β”‚   β”œβ”€β”€ schemaLoader.ts     # Schema file loader
β”‚   β”œβ”€β”€ dbClassifier.ts     # Database classifier
β”‚   └── router.ts           # API routes
β”œβ”€β”€ server.ts               # Express server
└── app.ts                  # Application entry point

Available Scripts

npm run dev      # Start development server with hot reload
npm run build    # Build TypeScript to JavaScript
npm start        # Start production server
npm run clean    # Clean build directory

Adding New Features

  1. New API Endpoint: Add to src/services/router.ts
  2. New Service: Create in src/services/ directory
  3. Configuration: Add to src/config/env.ts
  4. Types: Define in service files or create types/ directory

πŸ› Troubleshooting

Common Issues

Local Embeddings Not Working

Azure Embeddings Not Working

Service Won't Start

Debug Mode

Enable debug logging:

export DEBUG=*
npm run dev

πŸ“ˆ Performance Tips

🀝 Contributing

  1. Fork the repository
  2. Create a feature branch: git checkout -b feature-name
  3. Make your changes
  4. Add tests if applicable
  5. Commit your changes: git commit -m 'Add feature'
  6. Push to the branch: git push origin feature-name
  7. Submit a pull request

πŸ“„ License

This project is licensed under the MIT License - see the LICENSE file for details.

πŸ†˜ Support

For support and questions:


Made with ❀️ for intelligent database querying